package jdbc.dao.impl;

import jdbc.dao.EmployeeDao;
import jdbc.entity.Employee;
import jdbc.util.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Yancy
 * @program springioctest
 * @create 2021-01-23
 */
public class EmployeeDaoImpl implements EmployeeDao {
    @Override
    public int addEmployee(Employee employee) {
        String sql = "insert into employee value (?,?,?,?,?,?,?,?)";
        Object[] params = {0,employee.getEname(),employee.getJob(),employee.getMgr(),
        new java.sql.Date(employee.getHireDate().getTime()),employee.getSal(),employee.getComm(),employee.getDeptno()};
        int n = DbUtil.executeUpdate(sql,params);
        return n;
    }

    @Override
    public int deleteEmployee(int empNo) {
        return DbUtil.executeUpdate("delete from employee where emp_no=?",new Object[] {empNo});
    }

    @Override
    public int updateEmployee(Employee employee) {
        String sql = "update employee set emp_job = ?,emp_sal = ? where emp_no = ?";
        Object[] params = {employee.getJob(),employee.getSal(),employee.getEmpno()};
        return DbUtil.executeUpdate(sql,params);
    }

    @Override
    public Employee selectEmployee(int empNo) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Employee employee = null;
        // 2.获取连接
        connection = DbUtil.getConnection();

        try {
            statement = connection.createStatement();
            //执行SQL语句
            String sql = "select * from employee where emp_no="+empNo;
            resultSet = statement.executeQuery(sql);
            if(resultSet!=null && resultSet.next()){
                employee = new Employee();
                employee.setEmpno(resultSet.getInt("emp_no"));
                employee.setEname(resultSet.getString("emp_name"));
                employee.setJob(resultSet.getString("emp_job"));
                employee.setMgr(resultSet.getInt("emp_mgr"));
                employee.setHireDate(resultSet.getDate("emp_hireDate"));
                employee.setSal(resultSet.getDouble("emp_sal"));
                employee.setComm(resultSet.getDouble("emp_comm"));
                employee.setDeptno(resultSet.getInt("emp_deptNo"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employee;
    }

    @Override
    public List<Employee> selAllEmployee() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Employee> employeeList = new ArrayList<Employee>();
        try {

            // 2.获取连接
            connection = DbUtil.getConnection();

            statement = connection.createStatement();

            // 执行SQL语句
            String sqlString = "select * from employee";

            resultSet = statement.executeQuery(sqlString);
            if (resultSet!=null){
                while (resultSet.next()){
                    Employee employee = new Employee();
                    employee.setEmpno(resultSet.getInt("emp_no"));
                    employee.setEname(resultSet.getString("emp_name"));
                    employee.setJob(resultSet.getString("emp_job"));
                    employee.setMgr(resultSet.getInt("emp_mgr"));
                    employee.setHireDate(resultSet.getDate("emp_hireDate"));
                    employee.setSal(resultSet.getDouble("emp_sal"));
                    employee.setComm(resultSet.getDouble("emp_comm"));
                    employee.setDeptno(resultSet.getInt("emp_deptNo"));
                    employeeList.add(employee);
                }
            }else {
                return null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employeeList;
    }
}
